﻿
CREATE PROCEDURE [dbo].[CalcDisponibilPAC]
      @CodUnitate smallint,
      @IDPAC int,
      @Data datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAprob]= 
      (SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate
      WHERE (IDDocument IN 
       (SELECT IDDocument FROM RulajeDoc rd  WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContDebit LIKE LTRIM(RTRIM(es.ContBA)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END )
        -(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN 
        (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate AND DataDoc<=@Data)) AND ContCredit LIKE LTRIM(RTRIM(es.ContBA)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END)
            WHERE [IDPac]=@IDPAC

      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [CredBugAngaj]= (SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] 
      r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<@Data)) AND ContDebit LIKE LTRIM(RTRIM(es.ContAL)) +'_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END)
            -(SELECT coalesce(SUM(ValoareLei),0) FROM [Rulaje] r JOIN ExBugSetup es ON es.CodUnitate=r.CodUnitate WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
            IDJurnal = [PropunereAngajareCheltuialaDetaliu].[IDJurnal] AND [CodUnitate]=@CodUnitate  AND DataDoc<@Data)) AND ContCredit LIKE LTRIM(RTRIM(es.ContAL)) + '_________'+CASE WHEN LEN([PropunereAngajareCheltuialaDetaliu].[ArtAl])=4 THEN [PropunereAngajareCheltuialaDetaliu].[ArtAl]+'00' ELSE [PropunereAngajareCheltuialaDetaliu].[ArtAl] END) 
            --+(SELECT COALESCE(sum(Suma),0) FROM [PropunereAngajareCheltuialaDetaliu] a JOIN [PropunereAngajareCheltuiala] b ON a.IDPac=b.IDPAC  WHERE a.[IDPACDetaliu] < [PropunereAngajareCheltuialaDetaliu].[IDPACDetaliu] AND a.ArtAl = [PropunereAngajareCheltuialaDetaliu].[ArtAl]
            --AND b.Data < @Data)
            WHERE [IDPac]=@IDPAC
            
            
      UPDATE [PropunereAngajareCheltuialaDetaliu] SET [Clasif]=LTRIM(RTRIM(j.[Codificare]))+'.' +dbo.GetTitlu([ArtAl])+'.' + LTRIM(RTRIM([ArtAl]))  FROM [Jurnale] j WHERE j.[IDJurnal] = [PropunereAngajareCheltuialaDetaliu] .[IDJurnal] AND [IDPAC] =@IDPAC
END